﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using SiteManager;

public partial class BolgTest : System.Web.UI.Page
{
    // creating an instance of manager class
    Manager mgr = new Manager(true, "ConnectionString");
    Doctor objDoctor = new Doctor();
    int DoctorId = 0;
    int PatientId = 0;
    //string Title, Keywords, Description,desc;
    string desc;
    localFunction lfunction = new localFunction();
    protected void Page_Load(object sender, EventArgs e)
    {
        int id = 753459;
        BindBoxes(id);
        GetDoctorsBySpecialty(753459);
        
        GetDoctorsByState("210232,210237,211138,716240,210592,210233,210234,96665,211165,210235,96696,134844,77786,430817,210231,524809,668123");

    }

    private void BindBoxes(int id)
    {

        double overallRate = 0;

        {
            SqlParameter[] sqlParam = new SqlParameter[1];
            sqlParam[0] = new SqlParameter("@dr_id", SqlDbType.Int);
            sqlParam[0].Value = id;
            DataSet ds_drdet = mgr.ExecuteQuery("get_doctordetails", sqlParam, "tbl_a");

            if (ds_drdet.Tables[0].Rows.Count > 0)
            {
                if (ds_drdet.Tables[0].Rows[0]["featureType"].ToString() == "" || ds_drdet.Tables[0].Rows[0]["featureType"].ToString() == "0")
                {
                    //lnkSendMail.Visible = false;
                }

                if (ds_drdet.Tables[0].Rows[0]["email"].ToString().Trim().Length > 0)
                    Session["DentistDoctorEmail"] = ds_drdet.Tables[0].Rows[0]["email"].ToString();
                lblName.Text = ds_drdet.Tables[0].Rows[0]["Name"].ToString();
                lblTitle.Text = ds_drdet.Tables[0].Rows[0]["Title"].ToString();
                lblSpeciality.Text = Convert.ToBoolean(String.Compare(ds_drdet.Tables[0].Rows[0]["SpecialityID"].ToString(), "0")) ? ds_drdet.Tables[0].Rows[0]["SpecialityID"].ToString() : "";
               // lblPSpeciality.Text = Convert.ToBoolean(String.Compare(ds_drdet.Tables[0].Rows[0]["PrimarySpecialityID"].ToString(), "0")) ? ", " + ds_drdet.Tables[0].Rows[0]["PrimarySpecialityID"].ToString() : "";
               // lblSSpecialty.Text = Convert.ToBoolean(String.Compare(ds_drdet.Tables[0].Rows[0]["SecondarySpecialityID"].ToString(), "0")) ? ", " + ds_drdet.Tables[0].Rows[0]["SecondarySpecialityID"].ToString() : "";
                if (String.Compare(ds_drdet.Tables[0].Rows[0]["Abms_CertificationID"].ToString(), "False") == 0)
                { lblAbms.Text = "Not Board Certified"; }
                else
                { lblAbms.Text = "Board Certified"; }


                lblAddress.Text += ds_drdet.Tables[0].Rows[0]["Address1"].ToString();
                //if (ds_drdet.Tables[0].Rows[0]["Address2"].ToString().Trim().Length > 0)
                //    lblAddress.Text += "<br>" + ds_drdet.Tables[0].Rows[0]["Address2"].ToString();

                if (ds_drdet.Tables[0].Rows[0]["City"].ToString().Trim().Length > 0)
                    lblCity.Text += "<br>" + ds_drdet.Tables[0].Rows[0]["City"].ToString();
                if (ds_drdet.Tables[0].Rows[0]["State"].ToString().Trim().Length > 0)
                    lblState.Text += ", " + ds_drdet.Tables[0].Rows[0]["State"].ToString();
                if (ds_drdet.Tables[0].Rows[0]["Phone"].ToString().Trim().Length > 0)
                    lblPhone.Text += "<br>Phone :" + ds_drdet.Tables[0].Rows[0]["Phone"].ToString();
                if (ds_drdet.Tables[0].Rows[0]["Fax"].ToString().Trim().Length > 0)
                    lblPostalCode.Text += "<br>Fax :" + ds_drdet.Tables[0].Rows[0]["Fax"].ToString();



            }
            string[] strHosids = ds_drdet.Tables[0].Rows[0]["HospitalName"].ToString().Split(new char[] { ',' });
            if (ds_drdet.Tables[0].Rows[0]["HospitalName"].ToString() != "")
            {
                //DataSet dshos = mgr.ExecuteQuery("select HName from tbl_hospitals where id=" + ds_drdet.Tables[0].Rows[0]["HospitalName"].ToString(), "tbl");
                DataSet dshos = mgr.ExecuteQuery("select HName from tbl_hospitals where id in (select hospital_id from tbl_doctor_hospital where dr_id="
                    + id.ToString() + ");", "Hospitals");
                if (dshos.Tables[0].Rows.Count > 0)
                {
                    //lblHospital.Text = dshos.Tables[0].Rows[0]["HName"].ToString();

                    //changed as per client's request : K
                    //December 2, 20006
                    lblHospital.Text = "";
                    for (int i = 0; i <= dshos.Tables[0].Rows.Count - 1; i++)
                    {
                        lblHospital.Text += dshos.Tables[0].Rows[i]["HName"].ToString() + "<br>";
                    }
                    lblHospital.Text = lblHospital.Text.Substring(0, lblHospital.Text.Length - 4);
                }
                else
                {
                    lblHospital.Text = "None";
                }

            }

        }
    }

    private void GetDoctorsBySpecialty(int id)
    {
        DataSet ds = CustomRepository.CustumDataSetFunction("select *from tbl_doctors where id="+id.ToString()+"", "tbl");
        string  specID = ds.Tables[0].Rows[0]["specialityId"].ToString();
        string state=ds.Tables[0].Rows[0]["state"].ToString();
        ViewState["state"] = state;
        string city = ds.Tables[0].Rows[0]["zipcode"].ToString();
        DataSet dspec = mgr.ExecuteQuery(returnSql(specID, state),"tbl");
        TopDocRepeater.DataSource = dspec;
        TopDocRepeater.DataBind();
    }

    string returnSql(string speciality,string state)
    {
        string strSql = @"select qu from func_rating()";
        SqlDataReader dr = mgr.ExecuteQuery(strSql);

        if (dr.Read())
        {
            strSql = @" select top 10 d.city, d.zipcode,  
                        case when (select tbl_primaryspeciality.name from tbl_primaryspeciality where 
    					  tbl_primaryspeciality.name not in(Sp.name) and tbl_primaryspeciality.id=d.primaryspecialityid) is null
	                    then 
		                    (select tbl_secondaryspeciality.name from tbl_secondaryspeciality where
						    tbl_secondaryspeciality.id=d.secondaryspecialityid)
	                    else
		                    (select tbl_primaryspeciality.name from tbl_primaryspeciality where 
						    tbl_primaryspeciality.name not in(Sp.name) and tbl_primaryspeciality.id=d.primaryspecialityid)
	                    end 
                        as SubSpec,
                        rtrim((select name from tbl_states where stateId = d.state)) as state,
                        Sp.name as specialityName,
                        d.id as id,fname+' '+isnull(mname,'')+' '+lname as docname,address1+'<br>'+city+', '+state as address,
                         Sp.name+isnull(', '+(select tbl_primaryspeciality.name from tbl_primaryspeciality where 
						tbl_primaryspeciality.name not in(Sp.name) and tbl_primaryspeciality.id=d.primaryspecialityid)
						 ,'')+
						isnull(', ' + (select tbl_secondaryspeciality.name from tbl_secondaryspeciality where
						tbl_secondaryspeciality.name not in(Sp.name,p.name) and
						 tbl_secondaryspeciality.id=d.secondaryspecialityid )
                        ,'')
                        as speciality,
                       isnull(dp.website,'')as website,isnull(dp.photo,'') as photo,d.gender,d.Phone,d.Fax  
                        from tbl_doctors d
                        left outer join tbl_primaryspeciality p on p.id=d.primaryspecialityid 
                        left outer join tbl_secondaryspeciality s on s.id=d.secondaryspecialityid 
                        left outer join tbl_DrPremium dp on dp.dr_id=d.id
                        left outer join tbl_speciality sp on sp.id=D.specialityid                        
                        where  d.isdelete=0 and d.specialityId in(" + speciality + ") and d.state in('" + state + "')";
        }
        dr.Close();
        return strSql;

    }
    string returnSqlDocByState(string id)
    {
        string strSql = @"select qu from func_rating()";
        SqlDataReader dr = mgr.ExecuteQuery(strSql);

        if (dr.Read())
        {
            strSql = @" select top 10 d.city, d.zipcode,  
                        case when (select tbl_primaryspeciality.name from tbl_primaryspeciality where 
    					  tbl_primaryspeciality.name not in(Sp.name) and tbl_primaryspeciality.id=d.primaryspecialityid) is null
	                    then 
		                    (select tbl_secondaryspeciality.name from tbl_secondaryspeciality where
						    tbl_secondaryspeciality.id=d.secondaryspecialityid)
	                    else
		                    (select tbl_primaryspeciality.name from tbl_primaryspeciality where 
						    tbl_primaryspeciality.name not in(Sp.name) and tbl_primaryspeciality.id=d.primaryspecialityid)
	                    end 
                        as SubSpec,
                        rtrim((select name from tbl_states where stateId = d.state)) as state,
                        Sp.name as specialityName,
                        d.id as id,fname+' '+isnull(mname,'')+' '+lname as docname,address1+'<br>'+city+', '+state as address,
                         Sp.name+isnull(', '+(select tbl_primaryspeciality.name from tbl_primaryspeciality where 
						tbl_primaryspeciality.name not in(Sp.name) and tbl_primaryspeciality.id=d.primaryspecialityid)
						 ,'')+
						isnull(', ' + (select tbl_secondaryspeciality.name from tbl_secondaryspeciality where
						tbl_secondaryspeciality.name not in(Sp.name,p.name) and
						 tbl_secondaryspeciality.id=d.secondaryspecialityid )
                        ,'')
                        as speciality,
                       isnull(dp.website,'')as website,isnull(dp.photo,'') as photo,d.gender,d.Phone,d.Fax  
                        from tbl_doctors d
                        left outer join tbl_primaryspeciality p on p.id=d.primaryspecialityid 
                        left outer join tbl_secondaryspeciality s on s.id=d.secondaryspecialityid 
                        left outer join tbl_DrPremium dp on dp.dr_id=d.id
                        left outer join tbl_speciality sp on sp.id=D.specialityid                        
                        where  d.isdelete=0  and d.id in("+id+")";
        }
        dr.Close();
        return strSql;

    }

    private void GetDoctorsByState(string  id)
    {
        //DataSet ds = CustomRepository.CustumDataSetFunction("select *from tbl_doctors where id=" + id.ToString() + "", "tbl");
        //string specID = ds.Tables[0].Rows[0]["specialityId"].ToString();
        //string state = ds.Tables[0].Rows[0]["state"].ToString();
        //ViewState["state"] = state;
        //string city = ds.Tables[0].Rows[0]["zipcode"].ToString();
        DataSet dsState = mgr.ExecuteQuery(returnSqlDocByState(id), "tbl");
        rptDocByState.DataSource = dsState;
        rptDocByState.DataBind();

        //SqlParameter[] sqlParam = new SqlParameter[2];
        //sqlParam[0] = new SqlParameter("@state", SqlDbType.VarChar, 2);
        //sqlParam[1] = new SqlParameter("@TravelDistance", SqlDbType.Float);
        //sqlParam[0].Value = ViewState["state"].ToString();
        //sqlParam[1].Value =1;
        //DataSet ds_result = mgr.ExecuteQuery("HomepageDocSearch", sqlParam, "tbl_srch");
    }
}
